package databank;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class DataBank {

	// 첨부파일
	int fileSize = 10 * 1024 * 1024;
	String uploadPath = "C:\\Users\\jhta\\Desktop\\1403\\web999\\WebContent\\upload";
	String fileEncoding = "UTF-8";

	// jdbc연결
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.10.122:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";

	Connection conn;

	DataBankVo vo;

	
	public DataBank() {
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();

			// 2) Connection 객체생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean insert() {
		boolean flag = false;
		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(),
					uploadPath, fileSize, fileEncoding,
					new DefaultFileRenamePolicy());
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");
			
			if(sysfile==null){
				sysfile="NoFile";
			}
			
			System.out.println("mid : " + mid);
			System.out.println("ori : " + orifile);
			System.out.println("sys : " + sysfile);

			// db저장
			String sql = "insert into databank(serial, mid, subject, content, filename, pwd, mdate) values (seq_databank.nextval, ?,?,?,?,?,sysdate)";
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, mid);
				ps.setString(2, subject);
				ps.setString(3, content);
				ps.setString(4, sysfile);
				ps.setString(5, pwd);

				int cnt = ps.executeUpdate();
				if (cnt > 0) {
					flag = true;
				}
				ps.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

		return flag;
	}

	public Vector<DataBankVo> list() {

		Vector<DataBankVo> v = new Vector<>();
		String sql = "select * from databank where mid like ? or subject like ? or content like ? order by mdate desc";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				DataBankVo dv = new DataBankVo();

				dv.setSerial(rs.getInt("serial"));
				dv.setMid(rs.getString("mid"));
				dv.setSubject(rs.getString("subject"));
				dv.setMdate(rs.getDate("mdate"));
				dv.setFilename(rs.getString("filename"));

				v.add(dv);
			}

			rs.close();
			ps.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}

	public DataBankVo view() {
		DataBankVo dv = new DataBankVo();
		String sql = "select * from databank where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				dv.setSerial(rs.getInt("serial"));
				dv.setMid(rs.getString("mid"));
				dv.setMdate(rs.getDate("mdate"));
				dv.setPwd(rs.getString("pwd"));
				dv.setSubject(rs.getString("subject"));
				dv.setContent(rs.getString("content"));
				dv.setFilename(rs.getString("filename"));

			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dv;
	}
	
	public boolean delete(){
		boolean flag=false;
		String sql = "delete from databank where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt=ps.executeUpdate();
			if(cnt>0) {
				flag=true;
				File file = new File(getUploadPath() + "/" + vo.getFilename());
				if(file.exists()){
					file.delete();
				}else{
					//고민
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	public boolean modify(){
		boolean flag = false;
		
		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(),
					uploadPath, fileSize, fileEncoding,
					new DefaultFileRenamePolicy());

			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");
			int serial = Integer.parseInt(mr.getParameter("serial"));

			String oldfile = mr.getParameter("filename");
			String sysfile = mr.getFilesystemName("newfilename");
		
			
			if(sysfile==null){
				sysfile="NoFile";
			}
			
			//데이터베이스 수정
			PreparedStatement ps = null;
			String sql = null;
		
			if(oldfile==null||oldfile.equals("NoFile")){
				if(sysfile != null && !(sysfile.equals("NoFile"))){
					//첨부파일이 수정된 경우
					sql = "update databank set subject = ?, content=?, filename=? where serial=? and pwd=? ";
					ps = conn.prepareStatement(sql);
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setString(3, sysfile);
					ps.setInt(4, serial);
					ps.setString(5, pwd);
					
				}else{
					//첨부파일 수정없이 내용만 수정된경우
					sql = "update databank set subject = ?, content=? where serial=? and pwd=? ";
					ps = conn.prepareStatement(sql);
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setInt(3,serial);
					ps.setString(4, pwd);

				}
			}
			
			int cnt = ps.executeUpdate();
			if (cnt > 0) {
				flag = true;
			}
			ps.close();
			conn.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException se){
			se.printStackTrace();
		}
		
		return flag;
	}
	
	public void fileDel(){

		String sql = "update databank set filename='NoFile' where serial=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());
			
			int cnt=ps.executeUpdate();
			if(cnt>0) {
				File file = new File(getUploadPath() + "/" + vo.getFilename());
				if(file.exists()){
					file.delete();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public String getUploadPath() {
		return uploadPath;
	}

	public DataBankVo getVo() {
		return vo;
	}

	public void setVo(DataBankVo vo) {
		this.vo = vo;
	}

}
